package com.dxg.msns.user.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dxg.msns.common.pojo.UserHistory;
import com.dxg.msns.common.pojo.UserHistoryDetailVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 用户历史记录Mapper接口
 */
@Mapper
public interface UserHistoryMapper extends BaseMapper<UserHistory> {

    /**
     * 根据用户ID查询历史记录，按时间倒序
     * 
     * @param userId 用户ID
     * @return 历史记录列表
     */
    @Select("SELECT * FROM user_history WHERE user_id = #{userId} ORDER BY create_time DESC")
    List<UserHistory> selectByUserIdOrderByTime(@Param("userId") String userId);

    /**
     * 根据用户ID和音乐ID查询历史记录
     * 
     * @param userId  用户ID
     * @param musicId 音乐ID
     * @return 历史记录
     */
    @Select("SELECT * FROM user_history WHERE user_id = #{userId} AND music_id = #{musicId}")
    UserHistory selectByUserIdAndMusicId(@Param("userId") String userId, @Param("musicId") String musicId);

    /**
     * 更新历史记录的创建时间（用于重复播放时更新时间）
     * 
     * @param userId  用户ID
     * @param musicId 音乐ID
     * @return 影响行数
     */
    @Update("UPDATE user_history SET create_time = NOW() WHERE user_id = #{userId} AND music_id = #{musicId}")
    int updateCreateTimeByUserIdAndMusicId(@Param("userId") String userId, @Param("musicId") String musicId);

    /**
     * 根据用户ID统计历史记录总数
     * 
     * @param userId 用户ID
     * @return 记录总数
     */
    @Select("SELECT COUNT(*) FROM user_history WHERE user_id = #{userId}")
    Long countByUserId(@Param("userId") String userId);

    /**
     * 根据用户ID删除所有历史记录
     * 
     * @param userId 用户ID
     * @return 影响行数
     */
    @Update("DELETE FROM user_history WHERE user_id = #{userId}")
    int deleteByUserId(@Param("userId") String userId);

    /**
     * 查询用户历史记录详情（联查音乐信息和收藏状态）
     * 
     * @param userId 用户ID
     * @param offset 偏移量
     * @param limit  限制数量
     * @return 历史记录详情列表
     */
    @Select("SELECT " +
            "    uh.id, " +
            "    uh.user_id, " +
            "    uh.music_id, " +
            "    MAX(uh.create_time) AS create_time, " + // 获取每个 music_id 最新的记录
            "    m.music_name, " +
            "    m.music_author, " +
            "    m.music_cover_url, " +
            "    m.music_length, " +
            "    m.music_album, " +
            "    m.music_average_grade, " +
            "    m.music_url, " + // 新增的字段
            "    CASE WHEN mf.favor_status = 1 THEN true ELSE false END as is_favorite " +
            "FROM user_history uh " +
            "LEFT JOIN music m ON uh.music_id = m.music_id " +
            "LEFT JOIN music_favor mf ON uh.user_id = mf.collector_id AND uh.music_id = mf.music_id " +
            "WHERE uh.user_id = #{userId} " +
            "GROUP BY uh.user_id, uh.music_id, m.music_name, m.music_author, m.music_cover_url, " +
            "         m.music_length, m.music_album, m.music_average_grade, m.music_url, mf.favor_status " +  // 加入GROUP BY
            "ORDER BY create_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<UserHistoryDetailVO> selectUserHistoryDetailsByPage(@Param("userId") String userId,
                                                             @Param("offset") Integer offset,
                                                             @Param("limit") Integer limit);


    /**
     * 查询用户历史记录详情（不分页）
     * 
     * @param userId 用户ID
     * @return 历史记录详情列表
     */
    @Select("SELECT " +
            "    uh.id, " +
            "    uh.user_id, " +
            "    uh.music_id, " +
            "    uh.create_time, " +
            "    m.music_name, " +
            "    m.music_author, " +
            "    m.music_cover_url, " +
            "    m.music_length, " +
            "    m.music_album, " +
            "    m.music_average_grade, " +
            "    CASE WHEN mf.favor_status = 1 THEN true ELSE false END as is_favorite " +
            "FROM user_history uh " +
            "LEFT JOIN music m ON uh.music_id = m.music_id " +
            "LEFT JOIN music_favor mf ON uh.user_id = mf.collector_id AND uh.music_id = mf.music_id " +
            "WHERE uh.user_id = #{userId} " +
            "ORDER BY uh.create_time DESC")
    List<UserHistoryDetailVO> selectUserHistoryDetailsAll(@Param("userId") String userId);
}